package com.example.experiment4sqlite.utils

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.provider.BaseColumns
import android.text.Editable
import android.util.Log
import android.widget.Toast
import com.example.experiment4sqlite.TAG
import com.example.experiment4sqlite.bean.UserInfo
import java.util.*

object UserContract {
    // Table contents are grouped together in an anonymous object.
    object UserEntry : BaseColumns {
        const val TABLE_NAME = "user"
        const val USER_NAME = "userName"
        const val STU_NUMBER = "stuNumber"
        const val SEX = "sex"
        const val AGE = "age"
    }
}

private const val SQL_CREATE_ENTRIES =
    "CREATE TABLE ${UserContract.UserEntry.TABLE_NAME} (" +
            "${BaseColumns._ID} INTEGER PRIMARY KEY AUTOINCREMENT," +
            "${UserContract.UserEntry.USER_NAME} VARCHAR(20)," +
            "${UserContract.UserEntry.STU_NUMBER} VARCHAR(20)," +
            "${UserContract.UserEntry.SEX} VARCHAR(20)," +
            "${UserContract.UserEntry.AGE} INTEGER)"

private const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${UserContract.UserEntry.TABLE_NAME}"

class UserDbHelper(context: Context) : SQLiteOpenHelper(context,
    DATABASE_NAME, null,
    DATABASE_VERSION
) {

    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(SQL_CREATE_ENTRIES)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        db!!.execSQL(SQL_DELETE_ENTRIES)
        onCreate(db)
    }

    // Create
    fun addUser(db: SQLiteDatabase, userName: String, stuNumber: String, sex: String, age: Int) {
        val values = ContentValues().apply {
            put(UserContract.UserEntry.USER_NAME, userName)
            put(UserContract.UserEntry.STU_NUMBER, stuNumber)
            put(UserContract.UserEntry.SEX, sex)
            put(UserContract.UserEntry.AGE, age)
        }
        db.insert(UserContract.UserEntry.TABLE_NAME, null, values)
        db.close()
    }

    // Retrieve
    // 返回所有用户列表
    fun queryData(db: SQLiteDatabase): List<UserInfo>? {
        val cursor =
            db.query(UserContract.UserEntry.TABLE_NAME, null, null, null, null, null, "_id ASC")
        val list: MutableList<UserInfo> = ArrayList<UserInfo>()

        with (cursor) {
            while (moveToNext()) {
                val id = getInt(getColumnIndex(BaseColumns._ID))
                val username = getString(getColumnIndex("userName"))
                val stuNumber = getString(getColumnIndex("stuNumber"))
                val sex = getString(getColumnIndex("sex"))
                val age = getInt(getColumnIndex("age"))
                list.add(UserInfo(id, username, stuNumber, sex, age)
                )
            }
        }
        cursor.close()
        db.close()
        return list
    }

    /**
     * 通过学号，返回User，若返回 null说明无数据
     */
    fun search(db: SQLiteDatabase, stuNumber: String): UserInfo? {
        var ret: UserInfo?
        val cursor = db.query(UserContract.UserEntry.TABLE_NAME,
            null,
            "stuNumber = ?",
            arrayOf(stuNumber),
            null,
            null,
            null)
        with (cursor) {
            if (!cursor.moveToFirst())
            {
                ret =  null
            } else {
                val id = getInt(getColumnIndex(BaseColumns._ID))
                val username = getString(getColumnIndex("userName"))
                val stuNum = getString(getColumnIndex("stuNumber"))
                val sex = getString(getColumnIndex("sex"))
                val age = getInt(getColumnIndex("age"))
                ret =  UserInfo(id, username, stuNum, sex, age)
            }
        }
        cursor.close()
        db.close()
        return ret
    }

    // Update
    fun updateUser(db: SQLiteDatabase, id: Int, userName: String, stuNumber: String, sex: String, age: Int) {
        val values = ContentValues().apply {
            put(UserContract.UserEntry.USER_NAME, userName)
            put(UserContract.UserEntry.STU_NUMBER, stuNumber)
            put(UserContract.UserEntry.SEX, sex)
            put(UserContract.UserEntry.AGE, age)
        }
        //执行修改的方法
        db.update(UserContract.UserEntry.TABLE_NAME, values, "stuNumber = ?", arrayOf(stuNumber))
        db.close()
    }

    // Delete
    fun delUser(db: SQLiteDatabase, stuNumber: String) {
        //第一个参数：表名；第二个参数：需要删除的属性名，？代表占位符；第三个参数：属性名的属性值
//        db.delete(UserContract.UserEntry.TABLE_NAME, "id=?", arrayOf(id.toString() + ""))
        db.delete(UserContract.UserEntry.TABLE_NAME, "stuNumber=?", arrayOf(stuNumber))
        db.close()
    }

    companion object {
        // If you change the database schema, you must increment the database version.
        const val DATABASE_VERSION = 5
        const val DATABASE_NAME = "user_db"
    }
}